package com.eltsistemas.dao;

import java.sql.Date;
import java.util.Calendar;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import com.eltsistemas.modelo.Afastamento;

public class AfastamentoDao {
	
	private Session session;
	
	public AfastamentoDao(Session session)
	{
		this.session=session;
	}
	
	public Long verificaPeriodo(Calendar dtInicio, Calendar dtFim, Long id_Funcionario)
	{
		Date dtInicial = new Date(dtInicio.getTimeInMillis());
		Date dtFinal = new Date(dtFim.getTimeInMillis());
	
		StringBuilder consulta = new StringBuilder();
		consulta.append("SELECT MAX(a.id) from Afastamento as a WHERE ");
		consulta.append("(a.dataInicio >= :dtInicial or a.dataInicio >= :dtFinal or a.dataFim >= :dtInicial or a.dataFim >= :dtFinal) ");
		consulta.append("and id_funcionario=:id_Funcionario and a.status_Atual= :status");
		
		Query query = session.createQuery(consulta.toString());
		query.setDate("dtInicial", dtInicial);
		query.setDate("dtFinal", dtFinal);
		query.setLong("id_Funcionario", id_Funcionario);
		query.setBoolean("status", true);
		
		return (Long) query.uniqueResult();
	}
	
	@SuppressWarnings("unchecked")
	public List<Afastamento> verificaPeriodo_ForUpdate(Calendar dtInicio, Calendar dtFim, Long id_Afastamento, Long id_Funcionario)
	{
		Date dtInicial = new Date(dtInicio.getTimeInMillis());
		Date dtFinal = new Date(dtFim.getTimeInMillis());
		
		StringBuilder consulta = new StringBuilder();
		consulta.append("from Afastamento as a WHERE ");
		consulta.append("(a.dataInicio >= :dtInicial or a.dataInicio >= :dtFinal or a.dataFim >= :dtInicial or a.dataFim >= :dtFinal) ");
		consulta.append("and id != :id_Afastamento and id_funcionario=:id_Funcionario and a.status_Atual= :status");
		
		Query query = session.createQuery(consulta.toString());
		query.setDate("dtInicial", dtInicial);
		query.setDate("dtFinal", dtFinal);
		query.setLong("id_Afastamento", id_Afastamento);
		query.setLong("id_Funcionario", id_Funcionario);
		query.setBoolean("status", true);
		
		return query.list();
	}

	public Long verificaSeTemItem(Long id_Funcionario)
	{
		StringBuilder consulta = new StringBuilder();
		consulta.append("SELECT COUNT(af.id) from Afastamento as af WHERE ");
		consulta.append("af.funcionario.id= :id_Funcionario");
		
		Query query =this.session.createQuery(consulta.toString());
		query.setLong("id_Funcionario", id_Funcionario);
		
		Long qtd = (Long) query.uniqueResult();
		
		return qtd;
	}
	
	public Long verifica_IdMax(Long id_Funcionario)
	{
		StringBuilder consulta = new StringBuilder();
		consulta.append("SELECT MAX(af.id) from Afastamento as af WHERE ");
		consulta.append("af.funcionario.id= :id_Funcionario");
		
		Query query =this.session.createQuery(consulta.toString());
		query.setLong("id_Funcionario", id_Funcionario);
		
		Long qtd = (Long) query.uniqueResult();
		
		return qtd;
	}

	public Long verificaVigente(Long id_Funcionario, boolean status)
	{
		StringBuilder consulta = new StringBuilder();
		consulta.append("SELECT MAX(a.id) from Afastamento as a WHERE ");
		consulta.append("a.funcionario.id= :id_Funcionario and status_Atual= :status");
		
		Query query = session.createQuery(consulta.toString());
		query.setLong("id_Funcionario", id_Funcionario);
		query.setBoolean("status", status);
	
		Long id_AtualAfastamento = (Long) query.uniqueResult();
		
		return id_AtualAfastamento;
	}
}
